const pool = require('../config/database');

class HealthExaminationModel {
  /**
   * 创建新体检记录
   * @param {Object} examinationData - 体检数据
   * @returns {Promise<Object>} 创建的体检记录
   */
  static async create(examinationData) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // 构建字段和值数组
      const fields = Object.keys(examinationData);
      const values = Object.values(examinationData);
      const placeholders = fields.map(() => '?').join(', ');
      
      const sql = `
        INSERT INTO jktj_health_examination (${fields.join(', ')})
        VALUES (${placeholders})
      `;
      
      const [result] = await connection.execute(sql, values);
      
      await connection.commit();
      
      return {
        tj_id: result.insertId,
        ...examinationData
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据ID获取体检记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<Object>} 体检记录
   */
  static async findById(tjId) {
    const sql = 'SELECT * FROM jktj_health_examination WHERE tj_id = ?';
    const [rows] = await pool.execute(sql, [tjId]);
    return rows[0] || null;
  }

  /**
   * 根据档案ID获取体检记录列表
   * @param {number} daId - 档案ID
   * @returns {Promise<Array>} 体检记录列表
   */
  static async findByDaId(daId) {
    const sql = 'SELECT * FROM jktj_health_examination WHERE da_id = ? ORDER BY tjrq DESC';
    const [rows] = await pool.execute(sql, [daId]);
    return rows;
  }

  /**
   * 更新体检记录
   * @param {number} tjId - 体检ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<boolean>} 更新结果
   */
  static async update(tjId, updateData) {
    const fields = Object.keys(updateData);
    const values = Object.values(updateData);
    const setClause = fields.map(field => `${field} = ?`).join(', ');
    
    const sql = `UPDATE jktj_health_examination SET ${setClause} WHERE tj_id = ?`;
    const [result] = await pool.execute(sql, [...values, tjId]);
    
    return result.affectedRows > 0;
  }

  /**
   * 删除体检记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(tjId) {
    const sql = 'DELETE FROM jktj_health_examination WHERE tj_id = ?';
    const [result] = await pool.execute(sql, [tjId]);
    return result.affectedRows > 0;
  }

  /**
   * 获取体检记录列表（分页）
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 分页结果
   */
  static async findAll(options = {}) {
    const { page = 1, limit = 10, search = '', status = '' } = options;
    const offset = (page - 1) * limit;
    
    let whereClause = 'WHERE 1=1';
    const params = [];
    
    if (search) {
      whereClause += ' AND (da_id LIKE ? OR zrys_id LIKE ?)';
      params.push(`%${search}%`, `%${search}%`);
    }
    
    if (status) {
      whereClause += ' AND jkpjbm = ?';
      params.push(status);
    }
    
    const countSql = `SELECT COUNT(*) as total FROM jktj_health_examination ${whereClause}`;
    const [countResult] = await pool.execute(countSql, params);
    const total = countResult[0].total;
    
    const dataSql = `
      SELECT * FROM jktj_health_examination 
      ${whereClause} 
      ORDER BY tjrq DESC 
      LIMIT ? OFFSET ?
    `;
    const [rows] = await pool.execute(dataSql, [...params, limit, offset]);
    
    return {
      data: rows,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit)
    };
  }
}

module.exports = HealthExaminationModel;